﻿using KPIS.DBM;
using KPIS.GERP.GFMS.APP.App_UI.Commons;
using KPIS.GERP.GFMS.BLL;
using KPIS.GERP.GFMS.IBLL;
using KPIS.GERP.GFMS.MODEL;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;
using Telerik.Web.UI;

namespace KPIS.GERP.GFMS.APP.App_UI.Forms
{
    public partial class FIM_Prepare_Receive : System.Web.UI.Page
    {
        private Main main = new Main();
        private SsMng creatby = new SsMng();
        MailMerge mailMerge = new MailMerge();
        IBLL_CodeGenerate cg = new B_CodeGenerate();
        IBLL_HRM_PS_Employees emp = new B_HRM_PS_Employees();
        IBLL_FIM_Receives r = new B_FIM_Receives();
        IBLL_FIM_ReceiveDtls rd = new B_FIM_ReceiveDtls();
        IBLL_FIM_MoneyReturnDtls retd = new B_FIM_MoneyReturnDtls();
        IBLL_FIM_MAS_ReceiveTypes type = new B_FIM_MAS_ReceiveTypes();
        IBLL_FIM_MAS_BankAccounts bankacc = new B_FIM_MAS_BankAccounts();
        IBLL_FIM_MoneyRequestDtls mrd = new B_FIM_MoneyRequestDtls();
        IBLL_BGM_MAS_Source source = new B_BGM_MAS_Source();
        IBLL_BGM_ProjectOperationReport oprpt = new B_BGM_ProjectOperationReport();
        IBLL_FIM_MAS_ReceiveTypes rectype = new B_FIM_MAS_ReceiveTypes();
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        private enum EditorCommand
        {
            ViewAll, Insert, Update, AddNew, Edit, Detail, ViewAllDeduction,
            InsertDeduction, UpdateDeduction, AddNewDeduction, EditDeduction, DetailDeduction
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            //if (!Page.IsPostBack && Request["do"] == "Print1")
            //{
            //    Print1Report(Request["ReceiveDate"],Request["ReceiveMode"]);
            //    return;
            //}

            if (!IsPostBack)
            {
                LoadDDLFilterBgmSourceSeq();
                LoadDDLReceiveTypeSeq();
                LoadDDLBankAccSeq();
                ddlReceiveTypeSeq_SelectedIndexChanged(null, null);

                int year = main.GetSystemControls().default_bgm_year.Value;
                txtBgmYear.Text = year.ToString();
                txtBgmYear.MinValue = year - 10;
                txtBgmYear.MaxValue = year + 1;
                LoadGridIndex();
                //string source = "PrepareRec";
                //RadGridIndex.DataSource = r.GetFullPrepare(null, null, null, source);
                //RadGridIndex.DataBind();

                ChangeMode(Main.EditMode.View);
            }
        }

        protected void RadGridIndex_ItemCommand(object source, GridCommandEventArgs e)
        {
            string source1 = "PrepareRec";
            //ViewState["ReceiveModel"] = models;
            RadGridIndex.DataSource = r.GetFullPrepare(null, null, null, source1, null);
            RadGridIndex.DataBind();
        }

        public void LoadGrid()
        {
            List<M_FIM_Receives> models = new List<M_FIM_Receives>();
            string strDupReceiptNo = "";

            if (dgTemp.Items != null && ddlReceiveTypeSeq.SelectedValue != "")
            {
                foreach (GridDataItem item in dgTemp.Items)
                {
                    string strReceiptNo = main.WebString(item["receipt_no"].Text);
                    string strSQL = "select count(*) as row_count from fim_receives where record_status <> 'D' and receipt_no = @receipt_no";
                    try
                    {
                        dbManager.Open();
                        dbManager.CreateParameters(1);
                        dbManager.AddParameters(0, "@receipt_no", strReceiptNo);
                        DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                        if (dtb.Rows.Count > 0 && Convert.ToInt32(dtb.Rows[0][0]) > 0)
                        {
                            strDupReceiptNo = strReceiptNo;
                        }
                    }
                    catch
                    {

                    }
                    finally
                    {
                        dbManager.Dispose();
                    }
                }

                if (!string.IsNullOrEmpty(strDupReceiptNo))
                {
                    main.MessageAlert(Page, "เลขที่ใบเสร็จ " + strDupReceiptNo + " ซ้ำ");
                }
                else
                {
                    foreach (GridDataItem item in dgTemp.Items)
                    {
                        M_FIM_Receives model = new M_FIM_Receives();

                        int nReceiveTypeSeq = main.ConvertNullInt(ddlReceiveTypeSeq.SelectedValue) ?? 0;
                        int nBankAccSeq = main.ConvertNullInt(ddlBankAccSeq.SelectedValue) ?? 0;

                        model.receive_type_seq = nReceiveTypeSeq;
                        model.receive_type_name = ddlReceiveTypeSeq.Text;
                        model.bgm_source_seq = main.ConvertNullInt(ddlFilterBgmSourceSeq.SelectedValue);
                        model.bgm_source_name = ddlFilterBgmSourceSeq.Text;
                        model.ref_no = cg.GetNextCode(TransectionType.FimReceiptNo, (model.bgm_source_seq ?? 1) == 1 ? "1" : "2");
                        model.source = "PrepareRec";
                        model.print_out = 0;
                        //model.receive_type_seq = 20;
                        //model.receive_type_name = "รับเงินรายได้";
                        //model.bgm_source_seq = 11;
                        //model.bgm_source_name = "เงินรายได้";
                        model.bgm_year = main.ConvertNullInt(txtBgmYear.Text);
                        model.created_by = creatby.EmpSeqI;
                        model.receipt_no = main.WebString(item["receipt_no"].Text);
                        model.receive_date = main.ConvertNullDateTime(main.WebString(item["receive_date"].Text));
                        model.method_name = main.WebString(item["method_name"].Text);

                        if (model.method_name == "เงินสด")
                        {
                            model.method_seq = 1;
                        }
                        else if (model.method_name == "เช็ค")
                        {
                            model.method_seq = 2;
                        }
                        else if (model.method_name == "โอน")
                        {
                            model.method_seq = 3;
                        }

                        model.temp_contact = main.WebString(item["temp_contact"].Text);
                        model.include_vat = main.ConvertNullDecimal(main.WebString(item["include_vat"].Text));
                        model.amount = main.ConvertNullDouble(main.WebString(item["include_vat"].Text)); //ตอนนี้การนำเข้าจาก excel ยอดรวมทั้งสิ้น = ยอดเงินรวมภาษีมูลค่าเพิ่ม
                        model.vat_percent = main.ConvertNullDecimal(main.WebString(item["vat_percent"].Text));
                        model.tax_percent = 0;
                        model.vat_amount = main.ConvertNullDecimal(main.WebString(item["vat_amount"].Text));//main.ConvertNullDecimal(model.amount) - main.ConvertNullDecimal(model.exclude_vat);
                        model.exclude_vat = (main.ConvertNullDecimal(model.amount) ?? 0) - (model.vat_amount ?? 0);//main.ConvertNullDecimal(model.amount) * (100 / (100 + main.ConvertNullDecimal(model.vat_percent)));
                        model.contact_address = main.WebString(main.WebString(item["contact_address"].Text));
                        model.contact_tax_code = main.WebString(main.WebString(item["contact_tax_code"].Text));
                        model.remarks = main.WebString(main.WebString(item["remarks"].Text));
                        model.contact_branch = main.WebString(main.WebString(item["contact_branch"].Text));
                        model.receive_mode = "2";
                        model.manual_receipt = "1";
                        model.received_by = creatby.EmpSeqI;
                        model.created_when = main.ConvertNullDateTime(DateTime.Now);
                        model.bank_acc_seq = nBankAccSeq != 0 ? nBankAccSeq : 54;  //แก้ไขบัคข้อมูลแบงค์ไม่มี ทำให้ไม่พบข้อมูลตอนเรียนรายงานการรับเงิน
                        model.qty = main.ConvertNullInt(main.WebString(item["qty"].Text));
                        model.created_when = DateTime.Today;
                        model.unit_price = ((main.ConvertNullDecimal(model.amount) ?? 0) - (model.vat_amount ?? 0)) / main.ConvertNullDecimal(main.WebString(item["qty"].Text));

                        models.Add(model);

                        string receiveSeq = r.Insert(model);

                        model.receive_seq = int.Parse(receiveSeq);
                        RecivLog_Insert(model);
                    }

                    dgTemp.DataSource = null;
                    dgTemp.DataBind();
                    LoadGridIndex();
                    //string source = "PrepareRec";
                    ////ViewState["ReceiveModel"] = models;
                    //RadGridIndex.DataSource = r.GetFullPrepare(null, null, null, source);
                    //RadGridIndex.DataBind();
                }
            }
            //eturn models;
            Response.Redirect("FIM_Prepare_Receive.aspx");
        }

        public void LoadGridIndex()
        {
            string source = "PrepareRec";
            RadGridIndex.DataSource = r.GetFullPrepare(null, null, null, source, null);
            RadGridIndex.DataBind();
        }

        #region UiControl

        private void ChangeMode(Main.EditMode mode)
        {
            switch (mode)
            {
                case Main.EditMode.View:

                    RadGridIndex.Visible = true;
                    PanelAll.Visible = true;
                    pnlIndex.Visible = true;
                    pnlRadGrid.Visible = true;
                    pnlUpload.Visible = false;
                    pnlShowTemp.Visible = false;
                    btnConfirmt.Visible = false;
                    btnRollBack.Visible = false;
                    cmdPrint.Visible = true;
                    dtRecieve.Visible = true;

                    break;
                case Main.EditMode.AddNew:

                    RadGridIndex.Visible = false;
                    pnlUpload.Visible = true;
                    pnlIndex.Visible = false;
                    pnlRadGrid.Visible = false;
                    pnlShowTemp.Visible = false;
                    btnConfirmt.Visible = false;
                    btnRollBack.Visible = false;
                    lblRemin.Visible = false;
                    cmdPrint.Visible = false;
                    dtRecieve.Visible = false;

                    break;

                case Main.EditMode.ShowGrid:

                    RadGridIndex.Visible = false;
                    pnlUpload.Visible = false;
                    pnlIndex.Visible = false;
                    pnlRadGrid.Visible = false;
                    pnlShowTemp.Visible = true;
                    btnConfirmt.Visible = true;
                    btnRollBack.Visible = false;
                    lblRemin.Visible = false;
                    cmdPrint.Visible = false;
                    dtRecieve.Visible = false;

                    break;

                case Main.EditMode.Edit:

                    RadGridIndex.Visible = false;
                    pnlUpload.Visible = false;
                    pnlIndex.Visible = false;
                    pnlRadGrid.Visible = false;
                    pnlShowTemp.Visible = true;
                    btnConfirmt.Visible = false;
                    btnRollBack.Visible = true;
                    cmdPrint.Visible = false;
                    dtRecieve.Visible = false;

                    break;
            }

        }

        #endregion

        #region Report

        //protected void cmdPrint_Click(object sender, EventArgs e)
        //{
        //    if (!string.IsNullOrEmpty(dtRecieve.GetStringValue()))
        //    {
        //        string ReceiveDate = dtRecieve.GetStringValue();
        //        string ReceiveMode = ddlFilterReceiveType.SelectedValue;
        //        //string strReceiveSeq = main.WebString(RadGridIndex.Items[RadGridIndex.SelectedItems[0].ItemIndex]["receive_seq"].Text);
        //        //string strReceiptNo = main.WebString(RadGridIndex.Items[RadGridIndex.SelectedItems[0].ItemIndex]["receipt_no"].Text);

        //        Response.Redirect("FIM_Prepare_Receive.aspx?do=Print1&ReceiveDate=" + ReceiveDate + "&ReceiveMode=" + ReceiveMode);
        //    }
        //}


        //protected void Print1Report(string RecieveDate)
        //{
        //    int intprintOut = 1;
        //    int intUpdateBy = creatby.EmpSeqI;


        //    var employee = emp.GetInfo(Session["EmpSeq"].ToString());
        //    DataTable dtb = r.PrintHAInvoice(Convert.ToDateTime(RecieveDate));
        //    //Dictionary<string, string> dic = new Dictionary<string, string>();
        //    List<Dictionary<string,object>> dicList = new List<Dictionary<string,object>>();
        //    string[] arrSheetName = new string[dtb.Rows.Count];

        //    var stream = new MemoryStream();
        //    using (var package = new ExcelPackage(stream))
        //    {
        //        // add a new worksheet to the empty workbook
        //        var worksheet = package.Workbook.Worksheets.Add("testsheet");                

        //        for (int i = 0; i < dtb.Rows.Count; i++)
        //        {
        //            Dictionary<string, object> dic = new Dictionary<string, object>();
        //            DataRow dr = dtb.Rows[i];                    

        //            worksheet.Cells[i + 1, 1].Value = i.ToString();

        //            //string receiveType = ddlReceiveTypeSeq.SelectedValue;
        //            string receiveType = dr["receive_type_seq"].ToString();;
        //            //string receiveTypeName = ddlReceiveTypeSeq.Text;
        //            string receiveTypeName = dr["receive_type_name"].ToString();
        //            //string BgmSourceSeq = ddlFilterBgmSourceSeq.SelectedValue;
        //            string BgmSourceSeq = dr["bgm_source_seq"].ToString();
        //            string strAmount = string.Format(@"{0:#,##0.00}", dr["amount"]);
        //            string strExcludeVat = string.Format(@"{0:#,##0.00}", dr["exclude_vat"]);
        //            string strVatAmount = string.Format(@"{0:#,##0.00}", dr["vat_amount"]);
        //            string strReceiptNo = dr["receipt_no"].ToString();
        //            string receiveDate = dr["receive_date"] != null ? Convert.ToDateTime(dr["receive_date"]).ToShortDateString() : ""; 
        //            //Modified on 17/06/57
        //            //string TempContact = dr["temp_contact"] != null ? dr["temp_contact"].ToString() : "";
        //            string TempContact = (!dr.IsNull("NameTh")?dr["NameTh"].ToString():(dr["prefix_name"].ToString() + dr["first_name"].ToString() + " " + dr["last_name"].ToString()));
        //            //Modified on 17/06/57
        //            //string contactAddress = dr["contact_address"] != null ? dr["contact_address"].ToString() : "";
        //            string contactAddress = (!dr.IsNull("contact_address") ? dr["contact_address"].ToString() : ((dr["Building"].ToString()) + " " + (dr["Floor"].ToString()) + " " + (dr["Village"].ToString())
        //                                    + " " + (dr["Address"].ToString()) + " " + (dr["Road"].ToString()) + " " + ((!dr.IsNull("District") ? (dr["Province"].ToString().Contains("กรุงเทพ") == true ? "แขวง" + dr["District"].ToString() : "ตำบล" + dr["District"].ToString()) : ""))
        //                                    + " " + (!dr.IsNull("Amphur") ? (dr["Province"].ToString().Contains("กรุงเทพ") == true ? "เขต" + dr["Amphur"].ToString() : "อำเภอ" + dr["Amphur"].ToString()) : "")
        //                                    + " " + (!dr.IsNull("Province") ? (dr["Province"].ToString().Contains("กรุงเทพ") == true ? dr["Province"].ToString() : "จังหวัด" + dr["Province"].ToString()) : "")
        //                                    + " " + (dr["Postal"].ToString())));
        //            string contactTaxCode = dr["contact_tax_code"] != null ? dr["contact_tax_code"].ToString() : ""; 
        //            string remark = dr["remarks"].ToString(); 
        //            string strContactBranch = dr["contact_branch"] != null ? dr["contact_branch"].ToString() : ""; 

        //            string strSheetName = strReceiptNo.Length == 0 ? dr["receive_seq"].ToString() : strReceiptNo;
        //            arrSheetName[i] = strSheetName;

        //            //dic.Add(strSheetName + "!7:3", TempContact);
        //            //dic.Add(strSheetName + "!8:3", contactAddress);
        //            //dic.Add(strSheetName + "!8:16", receiveDate);
        //            //dic.Add(strSheetName + "!13:1", "1");
        //            //dic.Add(strSheetName + "!13:3", receiveTypeName);
        //            //dic.Add(strSheetName + "!13:13", "1");
        //            //dic.Add(strSheetName + "!13:15", strExcludeVat);
        //            //dic.Add(strSheetName + "!13:18", strExcludeVat);
        //            //dic.Add(strSheetName + "!24:1", main.MoneyTextThai(strAmount));
        //            //dic.Add(strSheetName + "!22:18", strExcludeVat);
        //            //dic.Add(strSheetName + "!23:18", strVatAmount);
        //            //dic.Add(strSheetName + "!24:18", strAmount);
        //            //dic.Add(strSheetName + "!28:1", employee.emp_name);
        //            //dic.Add(strSheetName + "!29:1", employee.job_site_name);
        //            //dic.Add(strSheetName + "!28:16", receiveDate);

        //            dic.Add("7:3", TempContact);
        //            dic.Add("8:3", contactAddress);
        //            dic.Add("8:16", receiveDate);
        //            dic.Add("13:1", "1");
        //            dic.Add("13:3", receiveTypeName);
        //            dic.Add("13:13", "1");
        //            dic.Add("13:15", strExcludeVat);
        //            dic.Add("13:18", strExcludeVat);
        //            dic.Add("24:1", main.MoneyTextThai(strAmount));
        //            dic.Add("22:18", strExcludeVat);
        //            dic.Add("23:18", strVatAmount);
        //            dic.Add("24:18", strAmount);
        //            dic.Add("29:1", employee.emp_name);
        //            dic.Add("30:1", employee.job_site_name);
        //            dic.Add("28:16", receiveDate);

        //            dicList.Add(dic);
        //        }
        //    }

        //    //mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/RETemplate.xlsx"), string.Format("ใบเสร็จรับเงิน-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, dic, null, "Template");
        //    mailMerge.WriteDicToExcel(Response, dicList, "ใบเสร็จรับเงิน-", Server.MapPath("~/Reports/Templates/"), "RETemplate.xlsx",arrSheetName);


        //    r.UpdatePrintOut(intUpdateBy, Convert.ToDateTime(RecieveDate), intprintOut, null);
        //    LoadGridIndex();
        //    //Response.Redirect("FIM_Prepare_Receive.aspx");
        //}

        //protected void Print1Report(string ReceiveDate,string Receivemode)
        //{
        //    string url = string.Format("ViewInvoiceReport.aspx?receivedate={0}&receivemode={1}", ReceiveDate,Receivemode);
        //    string s = "window.open('" + url + "', 'popup_window', 'width=750,height=600,left=100,top=100,resizable=yes');";
        //    ClientScript.RegisterStartupScript(this.GetType(), "script", s, true);
        //}

        #endregion

        #region Upload

        public DataTable GetDataTableFromExcel()
        {
            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                using (var stream = FileUpload.PostedFile.InputStream)
                {
                    pck.Load(stream);
                }

                var ws = pck.Workbook.Worksheets.First();
                DataTable dtb = new DataTable();
                bool hasHeader = true;

                foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                {
                    dtb.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
                }

                var startRow = hasHeader ? 2 : 1;

                for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                    var row = dtb.NewRow();
                    foreach (var cell in wsRow)
                    {
                        row[cell.Start.Column - 1] = cell.Text;
                    }

                    if (row[2].ToString().Trim() != "")
                    {
                        dtb.Rows.Add(row);
                    }
                }

                return dtb;
            }
        }

        private void ReceiveList(Stream file)
        {
            string errorResult = null;
            int created_by;
            Int32.TryParse(Convert.ToString(Session["EmpSeq"]), out created_by);

            try
            {
                using (ExcelPackage xlPackage = new ExcelPackage(file))
                {
                    ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
                    if (worksheet != null)
                    {
                        int yIndex = 2;
                        object v;
                        while ((v = worksheet.Cells["A" + (yIndex++)].Value) != null)
                        {
                            var title = v.ToString();
                            if (string.IsNullOrEmpty(title)) break;

                            string receiptNo = "A" + (yIndex - 1);
                            string receiveDate = "B" + (yIndex - 1);
                            string methodSeq = "C" + (yIndex - 1);
                            string receiveType = "D" + (yIndex - 1);
                            string TempContact = "E" + (yIndex - 1);
                            string amount = "F" + (yIndex - 1);
                            string vat = "G" + (yIndex - 1);
                            string vatAmount = "H" + (yIndex - 1);
                            string excludeVat = "I" + (yIndex - 1);
                            string contactAddress = "J" + (yIndex - 1);
                            string contactTaxCode = "K" + (yIndex - 1);
                            string remark = "L" + (yIndex - 1);
                            string refno = "M" + (yIndex - 1);
                            string qty = "N" + (yIndex - 1);
                            object reciveNoCell, receiveDateCell, methodSeqCell, receiveTypeCell, TempContactCell, amountCell
                                , vatCell, vatAmountCell, excludeVatCell, contactAddressCell, contactTaxCodeCell, remarkCell, refnoCell, qtyCell;

                            reciveNoCell = (worksheet.Cells[receiptNo].Value ?? new object());
                            receiveDateCell = (worksheet.Cells[receiveDate].Value ?? new object());
                            methodSeqCell = (worksheet.Cells[methodSeq].Value ?? new object());
                            receiveTypeCell = (worksheet.Cells[receiveType].Value ?? new object());
                            TempContactCell = (worksheet.Cells[TempContact].Value ?? new object());
                            amountCell = (worksheet.Cells[amount].Value ?? new object());
                            vatCell = (worksheet.Cells[amount].Value ?? new object());
                            vatAmountCell = (worksheet.Cells[vatAmount].Value ?? new object());
                            excludeVatCell = (worksheet.Cells[excludeVat].Value ?? new object());
                            contactAddressCell = (worksheet.Cells[contactAddress].Value ?? new object());
                            contactTaxCodeCell = (worksheet.Cells[contactTaxCode].Value ?? new object());
                            remarkCell = (worksheet.Cells[remark].Value ?? new object());
                            refnoCell = (worksheet.Cells[refno].Value ?? new object());
                            qtyCell = (worksheet.Cells[qty].Value ?? new object());
                        }

                        lblSuccess.Text = string.Format("[สำเร็จ] โปรแกรมได้นำเข้ารายการรับเงิน ประเภท {0}");
                        FileUpload.Enabled = false;
                        btnUpload.Enabled = false;
                    }
                }
            }
            catch (Exception ex)
            {
                errorResult = string.Format("[ไม่สำเร็จ] {0}", ex.Message);
            }

        }

        protected void ddlReceiveTypeSeq_SelectedIndexChanged(object o, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            string strReceiveTypeSeq = ddlReceiveTypeSeq.SelectedValue;

            if (strReceiveTypeSeq.Length > 0)
            {
                M_FIM_MAS_ReceiveTypes info = type.GetInfo(strReceiveTypeSeq);
                if (info.bank_acc_seq.HasValue)
                    ddlBankAccSeq.SelectedValue = info.bank_acc_seq.ToString();
            }
        }

        protected void LoadDDLReceiveTypeSeq()
        {
            ddlReceiveTypeSeq.DataSource = type.GetList(ddlFilterBgmSourceSeq.SelectedValue);
            ddlReceiveTypeSeq.DataBind();
        }

        protected void LoadDDLBankAccSeq()
        {
            ddlBankAccSeq.DataSource = bankacc.GetList(null, ddlFilterBgmSourceSeq.SelectedValue, null, null);
            ddlBankAccSeq.DataBind();
        }

        protected void LoadDDLFilterBgmSourceSeq()
        {
            ddlFilterBgmSourceSeq.DataSource = source.GetFull(true);
            ddlFilterBgmSourceSeq.DataBind();

            ddlFilterBgmSourceSeq.SelectedIndex = 0;
        }

        protected void btnUploadClick_Click(object sender, EventArgs e)
        {
            FileUpload.Enabled = true;
            btnUpload.Enabled = true;
            lblSuccess.Visible = false;
            ChangeMode(Main.EditMode.AddNew);
        }

        public DataTable LoadTemp()
        {
            string errorResult = null;
            DataTable dtb = GetDataTableFromExcel();

            List<M_FIM_Receives> models = new List<M_FIM_Receives>();

            if (dtb != null)
            {
                int CountT = 0;
                int CountF = 0;
                int CountSum = dtb.Rows.Count;

                for (int i = 0; i < dtb.Rows.Count; i++)
                {
                    DataRow dr = dtb.Rows[i];
                    M_FIM_Receives model = new M_FIM_Receives();

                    decimal dIncludeVat = main.ConvertNullDecimal(dr["จำนวนเงินรวมVat"]) ?? 0;
                    //double dAmount = dIncludeVat;
                    decimal dVatPercent = main.ConvertNullDecimal(dr["Vat%"]) ?? 0;
                    //double dVatAmount = setValueNumber(main.ConvertNullDouble(dr["จำนวนVat"]) ?? 0);
                    //double dExcludeVat = setValueNumber(main.ConvertNullDouble(dr["จำนวนไม่รวมVat"]) ?? 0);

                    decimal iQty;

                    try
                    {
                        iQty = main.ConvertNullDecimal(dr["จำนวนหน่วย"]).Value;
                    }
                    catch
                    {
                        iQty = 1;
                    }

                    model.receive_type_seq = main.ConvertNullInt(ddlReceiveTypeSeq.SelectedValue);
                    model.receive_type_name = ddlReceiveTypeSeq.Text;
                    model.bgm_source_seq = main.ConvertNullInt(ddlFilterBgmSourceSeq.SelectedValue);
                    model.bgm_source_name = ddlFilterBgmSourceSeq.Text;
                    model.bgm_year = main.ConvertNullInt(txtBgmYear.Text);
                    model.created_by = creatby.EmpSeqI;
                    model.receipt_no = dr["เลขที่ใบเสร็จรับเงิน"].ToString();
                    model.receive_date = main.ConvertNullDateTime(dr["วันที่รับเงิน"]);
                    model.method_seq = main.ConvertNullInt(dr["1=เงินสด 2=เช็ค 3=โอน"]);
                    model.qty = Convert.ToInt32(iQty);
                    model.unit_price = (dIncludeVat / iQty) * 100 / (100 + dVatPercent); // ราคาต่อหน่วยหาจาก ราคารวม หารด้วยจำนวนหน่วย แล้วถอดภาษีมูลค่าเพิ่ม
                    model.unit_price = setValueNumber(model.unit_price.Value);

                    decimal dExcludeVat = model.unit_price.Value * iQty;
                    decimal dVatAmount = dIncludeVat - dExcludeVat;

                    if (model.method_seq == 1)
                    {
                        model.method_name = "เงินสด";
                    }
                    else if (model.method_seq == 2)
                    {
                        model.method_name = "เงินสด";
                    }
                    else if (model.method_seq == 3)
                    {
                        model.method_name = "โอน";
                    }

                    model.temp_contact = dr["รับเงินจาก"].ToString();
                    model.include_vat = setValueNumber(dIncludeVat);
                    model.vat_percent = setValueNumber(dVatPercent);
                    model.tax_percent = 0;
                    model.exclude_vat = setValueNumber(dExcludeVat);
                    model.vat_amount = setValueNumber(dVatAmount);
                    model.tax = 0;

                    //model.amount = main.ConvertNullDouble(dr["จำนวนเงิน"]);
                    //model.vat_percent = main.ConvertNullDecimal(dr["Vat"]);
                    //model.tax_percent = main.ConvertNullDecimal(dr["Tax"]);
                    //model.exclude_vat = (main.ConvertNullDecimal(model.amount) ?? 0) * 100 / (100 + (main.ConvertNullDecimal(model.vat_percent) ?? 0));
                    //model.exclude_vat = Math.Round((model.exclude_vat ?? 0), 2);
                    //model.vat_amount = (main.ConvertNullDecimal(model.amount) ?? 0) - (main.ConvertNullDecimal(model.exclude_vat) ?? 0);
                    //model.vat_amount = Math.Round((model.vat_amount ?? 0), 2);
                    //model.tax = ;
                    model.contact_address = dr["ที่อยู่"].ToString();
                    model.contact_tax_code = dr["รหัสประจำตัวผู้สัยภาษี"].ToString();
                    model.remarks = dr["หมายเหตุ"].ToString();
                    model.contact_branch = dr["รหัสสาขา(สำนักงาน)"].ToString();
                    model.receive_mode = "1";
                    model.manual_receipt = "1";
                    model.received_by = creatby.EmpSeqI;
                    model.created_when = main.ConvertNullDateTime(DateTime.Now);
                    model.source = "PrepareRec";
                    model.print_out = 0;

                    string strReceiveTypeSeq = model.receive_type_seq.ToString();
                    string strBgmSourceSeq = model.bgm_source_seq.ToString();
                    string strYear = model.bgm_year.ToString();
                    string strCreatedBy = model.created_by.ToString();
                    string strReceiptNo = model.receipt_no.ToString();
                    DateTime? strReceiveDate = model.receive_date;
                    string strMethodSeq = model.method_seq.ToString();
                    string strTempContact = model.temp_contact.ToString();
                    string strIncludeVat = dIncludeVat.ToString();
                    string strVatPercent = dVatPercent.ToString();
                    string strTaxPercent = "0";
                    string strExcludeVat = dExcludeVat.ToString();
                    string strVatAmount = dVatAmount.ToString();
                    string strTaxAmount = "0";
                    string strContactAddress = model.contact_address.ToString();
                    string strContactTaxCode = model.contact_tax_code.ToString();
                    string strRemark = model.remarks.ToString();
                    string strContactBranch = model.contact_branch.ToString();
                    string strReceiveMode = model.receive_mode.ToString();
                    string strManualReceipt = model.manual_receipt.ToString();
                    string strReceivedBy = model.received_by.ToString();
                    string strSource = model.source;
                    int intPrintOut = model.print_out;
                    int intQty = model.qty.Value;

                    List<string> CheckValueT = new List<string>();
                    List<string> CheckValueF = new List<string>();
                    string flagTrue = "";
                    string flagFalse = "";

                    //IList<M_FIM_Receives> dtb2 = r.GetFull(null, null, null);
                    //List<int[]> listArray = new List<int[]>();

                    //int n = dtb2.Count;
                    //int j;
                    //foreach (var list in dtb2)
                    //{
                    //    for (j = 0; j <= n; j++)
                    //    {
                    //        if (list.receipt_no == strReceiptNo)
                    //        {
                    //            lblRemin.Text = "อัพโหลดข้อมูลไม่สำเร็จ  กรุณาอัพโหลดข้อมูลใหม่อีกครั้ง";
                    //            lblRemin.Visible = true;
                    //            ChangeMode(Main.EditMode.View);
                    //        }
                    //    }
                    //}

                    bool CheckValueSubmit = CheckData(strReceiveTypeSeq, strBgmSourceSeq, strYear, strCreatedBy, strReceiptNo, strReceiveDate, strMethodSeq
                                  , strTempContact, strIncludeVat, strVatPercent, strTaxPercent, strVatAmount, strTaxAmount, strExcludeVat
                                  , strContactAddress, strContactTaxCode, strRemark, strContactBranch, strReceiveMode
                                  , strManualReceipt, strReceivedBy, strSource, intPrintOut, intQty);

                    if (CheckValueSubmit == true)
                    {
                        flagTrue = i.ToString();
                        CheckValueT.Add(flagTrue);
                        CountT = CountT + 1;

                        models.Add(model);
                    }
                    else
                    {
                        flagFalse = i.ToString();
                        CheckValueF.Add(flagFalse);
                        CountF = CountF + 1;
                    }

                    if (CountSum == CountT)
                    {
                        // ViewState["ReceiveModel"] = models;
                        dgTemp.DataSource = models;
                        dgTemp.DataBind();
                    }

                    lblSuccess.Text = string.Format("[สำเร็จ] โปรแกรมได้นำเข้าจัดเตรียมการรับเงิน");
                    lblSuccess1.Text = CountT.ToString();
                    lblFail.Text = CountF.ToString();
                    //lblListFail.Text = 
                    lblSumList.Text = CountSum.ToString();
                    FileUpload.Enabled = false;
                    btnUpload.Enabled = false;


                    //dgTemp.DataSource = models;
                    //dgTemp.DataBind();

                    if (CountF > 0)
                    {
                        lblRemin.Text = "อัพโหลดข้อมูลไม่สำเร็จ  กรุณาอัพโหลดข้อมูลใหม่อีกครั้ง";
                        lblRemin.Visible = true;
                        ChangeMode(Main.EditMode.Edit);
                    }
                    else
                    {
                        ChangeMode(Main.EditMode.ShowGrid);
                    }

                }
            }
            else
            {
                errorResult = string.Format("[ไม่สำเร็จ] {0}");
            }

            return dtb;


        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            LoadTemp();
            ChangeMode(Main.EditMode.ShowGrid);
        }

        protected void btnConfirmt_Click(object sender, EventArgs e)
        {
            LoadGrid();
            ChangeMode(Main.EditMode.View);
            lblRemin.Text = "อัพโหลดข้อมูลสำเร็จ";
            lblRemin.Visible = true;
            cmdPrint.Visible = true;
            dtRecieve.Visible = true;
        }

        protected void btnRollBack_Click(object sender, EventArgs e)
        {
            ChangeMode(Main.EditMode.View);
        }

        #endregion

        #region Temp

        protected bool CheckData(string strReceiveTypeSeq, string strBgmSourceSeq, string strYear, string strCreatedBy
                        , string strReceiptNo, DateTime? strReceiveDate, string strMethodSeq, string strTempContact
                        , string strAmount, string strVatPercent, string strTaxPercent, string strVatAmount, string strTaxAmount, string strExcludeVat
                        , string strContactAddress, string strContactTaxCode, string strRemark, string strContactBranch
                        , string strReceiveMode, string strManualReceipt, string strReceivedBy, string strSource, int intPrintOut, int intQty)
        {
            string strSQL;
            bool result = false;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(24);
                dbManager.AddParameters(0, "@receive_type_seq", strReceiveTypeSeq);
                dbManager.AddParameters(1, "@bgm_source_seq", strBgmSourceSeq);
                dbManager.AddParameters(2, "@bgm_year", strYear);
                dbManager.AddParameters(3, "@created_by", strCreatedBy);
                dbManager.AddParameters(4, "@receipt_no", strReceiptNo);
                dbManager.AddParameters(5, "@receive_date", strReceiveDate);
                dbManager.AddParameters(6, "@method_seq", strMethodSeq);
                dbManager.AddParameters(7, "@temp_contact", strTempContact);
                dbManager.AddParameters(8, "@amount", strAmount);
                dbManager.AddParameters(9, "@vat_percent", strVatPercent);
                dbManager.AddParameters(10, "@tax_percent", strTaxPercent);
                dbManager.AddParameters(11, "@vat_amount", strVatAmount);
                dbManager.AddParameters(12, "@exclude_vat", strExcludeVat);
                dbManager.AddParameters(13, "@contact_address", strContactAddress);
                dbManager.AddParameters(14, "@contact_tax_code", strContactTaxCode);
                dbManager.AddParameters(15, "@remarks", strRemark);
                dbManager.AddParameters(16, "@contact_branch", strContactBranch);
                dbManager.AddParameters(17, "@receive_mode", strReceiveMode);
                dbManager.AddParameters(18, "@manual_receipt", strManualReceipt);
                dbManager.AddParameters(19, "@received_by", strReceivedBy);
                dbManager.AddParameters(20, "@source", strSource);
                dbManager.AddParameters(21, "@print_out", intPrintOut);
                dbManager.AddParameters(22, "@tax", strTaxAmount);
                dbManager.AddParameters(23, "@qty", intQty);

                strSQL = " create table #tempReceive ( receive_type_seq int"
                    + " ,bgm_source_seq int"
                    + " ,bgm_year int"
                    + " ,created_by int"
                    + " ,receipt_no varchar(50)"
                    + " ,receive_date date"
                    + " ,method_seq int"
                    + " ,temp_contact varchar(255)"
                    + " ,amount decimal(18, 2)"
                    + " ,vat_percent decimal(18, 2)"
                    + " ,tax_percent decimal(18, 2)"
                    + " ,vat_amount decimal(18, 2)"
                    + " ,tax decimal(18, 2)"
                    + " ,exclude_vat decimal(18, 2)"
                    + " ,contact_address nvarchar(2000)"
                    + " ,contact_tax_code varchar(20)"
                    + " ,remarks varchar(2000)"
                    + " ,contact_branch varchar(225)"
                    + " ,receive_mode char(1)"
                    + " ,manual_receipt char(1)"
                    + " ,received_by int "
                    + ", source varchar(255) "
                    + ", print_out int"
                    + ", qty int)"

                    + " insert into #tempReceive (receive_type_seq, bgm_source_seq ,bgm_year, created_by, "
                    + " receipt_no ,receive_date, method_seq, temp_contact, amount, vat_percent, tax_percent"
                    + " ,vat_amount, tax, exclude_vat , contact_address, contact_tax_code  "
                    + " , remarks, contact_branch, receive_mode, manual_receipt, received_by, source, print_out, qty)"
                    + " VALUES (@receive_type_seq"
                    + " ,@bgm_source_seq"
                    + " ,@bgm_year"
                    + " ,@created_by"
                    + " ,@receipt_no"
                    + " ,@receive_date"
                    + " ,@method_seq"
                    + " ,@temp_contact"
                    + " ,@amount"
                    + " ,@vat_percent"
                    + " ,@tax_percent"
                    + " ,@vat_amount"
                    + " ,@tax"
                    + " ,@exclude_vat"
                    + " ,@contact_address"
                    + " ,@contact_tax_code"
                    + " ,@remarks"
                    + " ,@contact_branch "
                    + " ,@receive_mode"
                    + " ,@manual_receipt"
                    + " ,@received_by "
                    + " ,@source "
                    + " ,@print_out "
                    + " ,@qty )"

                    + " select * from #tempReceive "
                    + " DROP TABLE #tempReceive";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    result = true;
                    break;
                }

                //DataTable dtb = GetDataTableFromExcel();
                //int CountT = 0;
                //int CountSum = dtb.Rows.Count;

                //if (result == true)
                //{
                //    CountT = CountT + 1;
                //}

                //if (CountT == CountSum)
                //{
                //    strSQL = "insert into fim_receives Select *  from tempReceive";
                //    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                //}
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return result;

        }

        #endregion

        protected void ddlFilterBgmSourceSeq_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            LoadDDLReceiveTypeSeq();
            LoadDDLBankAccSeq();
            ddlReceiveTypeSeq_SelectedIndexChanged(null, null);
        }

        protected void ddlFilterReceiveType_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            hdReceiveDate.Value = dtRecieve.GetStringValue();
            hdReceiveMode.Value = ddlFilterReceiveType.SelectedValue;
        }

        private void RecivLog_Insert(M_FIM_Receives model)
        {
            string strConn = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"];
            string strSQL = string.Format("insert into fim_receives_log (receivelog_date,bgm_year,receipt_no,ref_no,receivelog_remark,record_status,receive_mode,input_source,created_by,created_when,model.receive_seq,bgm_source_seq) values ('{0}',{1},'{2}','{3}','{4}','{5}','{6}','{7}',{8},'{9}',{10},{11})"
                , string.Format("{0}-{1}-{2}", (DateTime.Today).Year > 2500 ? (DateTime.Today).Year - 543 : (DateTime.Today).Year, (DateTime.Today).Month, (DateTime.Today).Day)
                , model.bgm_year, model.receipt_no, model.ref_no, "", "I", model.receive_mode, model.source, model.created_by
                , string.Format("{0}-{1}-{2}", model.created_when.Value.Year > 2500 ? model.created_when.Value.Year - 543 : model.created_when.Value.Year
                , model.created_when.Value.Month, model.created_when.Value.Day), model.receive_seq, 11);

            using (SqlConnection conn = new SqlConnection(strConn))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(strSQL, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public decimal setValueNumber(decimal value)
        {
            string[] strPre = value.ToString().Split('.');

            if (strPre.Length > 1 && strPre[1].Length > 2)
            {
                strPre[1] = strPre[1].Substring(0, 2);
                value = Convert.ToDecimal(strPre[0] + "." + strPre[1]);
            }

            return value;
        }

        public double setValueNumber(double value)
        {
            string[] strPre = value.ToString().Split('.');

            if (strPre.Length > 1 && strPre[1].Length > 2)
            {
                strPre[1] = strPre[1].Substring(0, 2);
                value = Convert.ToDouble(strPre[0] + "." + strPre[1]);
            }

            return value;
        }

        public int setValueNumber(int value)
        {
            string[] strPre = value.ToString().Split('.');

            if (strPre.Length > 1 && strPre[1].Length > 2)
            {
                strPre[1] = strPre[1].Substring(0, 2);
                value = Convert.ToInt32(strPre[0] + "." + strPre[1]);
            }

            return value;
        }
    }
}